#!/bin/perl
use strict;
use DBI();
use DateTime;
my $dbname="confluence";
my $dbhost="bass.netcracker.com";
my $user = "inot";
my $password = "000000";
my $days = '90';
my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",
                         $user, $password,{'RaiseError' => 1});

my $sth = $dbh->prepare("SELECT entity_name, unix_timestamp(date_val) as lastlog FROM `confluence`.`OS_PROPERTYENTRY` where entity_key like '%previous.login.date%' and entity_name like 'EXT_%'");
$sth->execute;
my $dtnow = DateTime->now(time_zone => 'Europe/Moscow');
my $unixtime = $dtnow->epoch();
my $i=1;
while (my $ref = $sth->fetchrow_hashref()) {
	my $name =  substr ($ref->{'entity_name'},4); 
	my $lstlog = $ref->{'lastlog'};
	my $result = int (($unixtime - $lstlog)/60/60/24);
	my $localtime = localtime($lstlog);
	if ($result < $days or $result > 14000) {
		if ($result > 14550) {
			$result = 'Never Login';
			print "Login - $name :  $result \n";
		} else {print "Login - $name : Last login $result days ago. Last Login = $localtime \n";}
	$i++;
	}
	else
	{next;}
}

print "\nLess than $days ago accessed $i users.\n";
$dbh->disconnect();
                         


